package tw.com.dao.report04;

import java.util.List;

import tw.com.to.report04.ProductResultTO;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class ProductComplainDAOImpl implements ProductComplainDAO {
	@Autowired
    private SessionFactory sessionFactory;

	@Override
	public List<ProductResultTO> listYearCases(String determine,String startYear, String endYear) {
		// TODO Auto-generated method stub
		String sqldetermine="";
		if(determine.equals("Y")){
			sqldetermine="(Determine = 'Y')";
		}else if(determine.equals("N")){
			sqldetermine="(Determine = 'N')";
		}else if(determine.equals("ALL")){
			sqldetermine="(Determine IN ('Y', 'N'))";
		}
		String sql=
		"SELECT ProductType +'Y'+ CONVERT(varchar(4), Year) AS ID, ProductType, Year,0 AS Quarter, 0 AS Month , SUM(ComplainCase) AS ComplainCase"+
		"    FROM (SELECT ProductType, DATEPART(yyyy, StartDate) AS Year, DATEPART(qq, StartDate) AS Quarter, DATEPART(mm, StartDate) AS Month, COUNT(*) AS ComplainCase"+
		"              FROM CDI "+
		"              WHERE "+sqldetermine+" AND (StartDate BETWEEN '"+startYear+"-1-1 00:00:00' AND '"+endYear+"-1-1 00:00:00')"+
		"              GROUP BY ProductType, DATEPART(yyyy, StartDate), DATEPART(qq, StartDate), DATEPART(mm, StartDate)" +
		"          ) AS derivedtbl_1 "+
		"GROUP BY ProductType, Year";
		
		return sessionFactory.getCurrentSession().createSQLQuery(sql).addEntity(ProductResultTO.class).list();
	}

	@Override
	public List<ProductResultTO> listQuarterCases(String determine,String startYear,String endYear) {
		// TODO Auto-generated method stub
		String sqldetermine="";
		if(determine.equals("Y")){
			sqldetermine="(Determine = 'Y')";
		}else if(determine.equals("N")){
			sqldetermine="(Determine = 'N')";
		}else if(determine.equals("ALL")){
			sqldetermine="(Determine IN ('Y', 'N'))";
		}		
		String sql=
			"SELECT ProductType +'Y'+ CONVERT(varchar(4), Year) +'Q'+ CONVERT(varchar(4), Quarter) AS ID, ProductType, Year, Quarter,0 AS Month , SUM(ComplainCase) AS ComplainCase"+
			"    FROM (SELECT ProductType, DATEPART(yyyy, StartDate) AS Year, DATEPART(qq, StartDate) AS Quarter, DATEPART(mm, StartDate) AS Month, COUNT(*) AS ComplainCase"+
			"              FROM CDI "+
			"              WHERE "+sqldetermine+" AND (StartDate BETWEEN '"+startYear+"-1-1 00:00:00' AND '"+endYear+"-1-1 00:00:00')"+
			"              GROUP BY ProductType, DATEPART(yyyy, StartDate), DATEPART(qq, StartDate), DATEPART(mm, StartDate)" +
			"          ) AS derivedtbl_1 "+
			"GROUP BY ProductType, Year, Quarter";
			
			return sessionFactory.getCurrentSession().createSQLQuery(sql).addEntity(ProductResultTO.class).list();
	}

	@Override
	public List<ProductResultTO> listMonthCases(String determine,String startYear, String endYear) {
		// TODO Auto-generated method stub
		String sqldetermine="";
		if(determine.equals("Y")){
			sqldetermine="(Determine = 'Y')";
		}else if(determine.equals("N")){
			sqldetermine="(Determine = 'N')";
		}else if(determine.equals("ALL")){
			sqldetermine="(Determine IN ('Y', 'N'))";
		}
		String sql=
			"SELECT ProductType +'Y'+ CONVERT(varchar(4), Year) +'Q'+ CONVERT(varchar(4), Quarter) +'M'+ CONVERT(varchar(4), Month) AS ID, ProductType, Year, Quarter, Month ,SUM(ComplainCase) AS ComplainCase"+
			"    FROM (SELECT ProductType, DATEPART(yyyy, StartDate) AS Year, DATEPART(qq, StartDate) AS Quarter, DATEPART(mm, StartDate) AS Month, COUNT(*) AS ComplainCase"+
			"              FROM CDI "+
			"              WHERE "+sqldetermine+" AND (StartDate BETWEEN '"+startYear+"-1-1 00:00:00' AND '"+endYear+"-1-1 00:00:00')"+
			"              GROUP BY ProductType, DATEPART(yyyy, StartDate), DATEPART(qq, StartDate), DATEPART(mm, StartDate)" +
			"          ) AS derivedtbl_1 "+
			"GROUP BY ProductType, Year, Quarter, Month";

			return sessionFactory.getCurrentSession().createSQLQuery(sql).addEntity(ProductResultTO.class).list();
	}

}
